USE [rti-clients]
GO
/****** Object:  StoredProcedure [dbo].[GetDevicesDependFirewallByAccountIdAndFirewallId]    Script Date: 12/17/2012 10:11:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [dbo].[GetDevicesDependFirewallByAccountIdAndFirewallId]
	@AccountId INT
	, @FirewallId int
	
AS
BEGIN
	SET NOCOUNT ON

	select A.*, case when B.DeviceId IS null then 0 else 1 end [IsCheck]
	from
	(
		SELECT F.*, A.EffFromDate, A.EffToDate, A.Fee [DeviceFee], A.DiskQuota, A.RAM, A.vCPU [CPU], B.AutoId [OSId], B.TypeName [OSName], A.Bandwidth
			,  F.IPAddress [DeviceIP], F.Name [DeviceName],  null [FirewallIP], null [FirewallName], null [FirewallNo], F.AutoId [DeviceId]
		FROM Devices F
			left join WindowsType B on F.WindowId = B.AutoId
			 LEFT JOIN
			 (
				SELECT A.DeviceId, A.EffFromDate, B.EffToDate, B.Fee, B.DiskQuota, B.RAM, B.vCPU, B.Bandwidth
				FROM (
						SELECT A.DeviceId, MAX(EffFromDate) [EffFromDate]
						FROM DeviceRent A
						WHERE NOT EffFromDate IS NULL
						GROUP BY DeviceId, EffToDate
					 ) A 
					INNER JOIN DeviceRent B ON A.DeviceId = B.DeviceId AND A.EffFromDate = B.EffFromDate
			 ) A ON F.AutoId = A.DeviceId		  
		WHERE F.AccountId = @AccountId
	)A
	left join
	(
		select distinct A.DeviceId
		from FirewallDeviceAssociation A inner join Firewalls B on A.FirewallId = B.AutoId
		where A.FirewallId = @FirewallId and B.AccountId = @AccountId
	)B on A.DeviceId = B.DeviceId
	
	SET NOCOUNT Off
END
